package org.nativejpa.query;

import org.apache.velocity.app.event.ReferenceInsertionEventHandler;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;

import javax.persistence.Query;
import javax.persistence.PersistenceException;
import javax.persistence.Entity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Arrays;

import org.nativejpa.EntityManagerExt;
import org.nativejpa.transaction.TransactionConnectionManager;
import org.nativejpa.transaction.EntityTransactionImpl;
import org.nativejpa.cache.CacheFactory;
import org.nativejpa.mapping.MappingEntity;
import org.nativejpa.mapping.EntityFactory;
import org.nativejpa.mapping.JoinColumnMetadata;
import org.nativejpa.mapping.EntityRepository;
import org.nativejpa.mapping.ColumnMetadata;
import org.nativejpa.util.VelocityUtils;
import org.nativejpa.util.ClassUtils;

/**
 * @author <a href="mailto:jfox.young@gmail.com">Young Yang</a>
 * @create 2009-3-18 13:43:53
 */
public class SQLQuery extends QueryExt {

    static Log logger = LogFactory.getLog(SQLQuery.class);

    private EntityManagerExt em;
    private Connection connection;
    private SQLTemplate sqlTemplate = null;

    //the paramaterMap for VelocityContext
    private Map<String, Object> parameterMap = new HashMap<String, Object>();

    private String nativeSQL;

    private boolean isNamedQuery = false;

    // query's result, should be set before call CacheQuerySynchronization, so Synchronization can use the result
    private Object result;

    private JDBCMapper jdbcMapper = null;

    public SQLQuery(EntityManagerExt em, SQLTemplate sqlTemplate) {
        this.em = em;
        this.sqlTemplate = sqlTemplate;
        if (sqlTemplate instanceof NamedSQLTemplate) {
            isNamedQuery = true;
        }
        this.jdbcMapper = JDBCMapper.getJDBCMapper(em.getEntityManagerFactory().getDatabaseProductName());
    }

    public SQLQuery(EntityManagerExt em, SQLTemplate sqlTemplate, Connection connection) {
        this(em,sqlTemplate);
        this.connection = connection;
    }

    public boolean isNamedQuery() {
        return isNamedQuery;
    }

    public String getName() {
        if (sqlTemplate instanceof NamedSQLTemplate) {
            return ((NamedSQLTemplate)sqlTemplate).getName();
        }
        else {
            return "";
        }
    }

    public SQLTemplate getSQLTemplate() {
        return sqlTemplate;
    }

    public Query setParameter(String name, Object value) {
        parameterMap.put(name, value);
        return this;
    }

    public Map<String, Object> getParameterMap(){
        return Collections.unmodifiableMap(parameterMap);
    }

    public Object getResult() {
        return result;
    }

    public void setResult(Object result) {
        this.result = result;
    }

    public int executeUpdate() {
        em.beforeExecuteUpdate(this);
        PreparedStatement pst = null;
        try {
            pst = buildPreparedStatement();
            return pst.executeUpdate();
        }
        catch (SQLException e) {            
            throw new PersistenceException("SQLQuery.executeUpdate exception.", e);
        }
        finally {
            try {
            // close PreparedStatement
                if(pst != null) {
                    pst.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
            em.afterExecuteUpdate(this,true);
        }
    }

    public List<?> getResultList() {
        em.beforeExecuteQuery(this);
        if (getResult() != null) {
            return (List<?>)getResult(); // object get from cache is array, not list
        }

        PreparedStatement pst = null;
        ResultSet rset = null;
        final List<Object> results = new ArrayList<Object>();
        try {
            pst = buildPreparedStatement();
            rset = pst.executeQuery();
            // Skip Results
            if (getFirstResult() > 0) {
                try {
                    rset.absolute(getFirstResult()); // absolute start from 1, but firstResult start 0
                }
                catch(SQLException e) { // not support
                    logger.warn("Not support ResultSet.absolute, use ResultSet.next, may cause performance issue.", e);
                    for (int i = 0; i < getFirstResult(); i++) {
                        if (!rset.next()) {
                            return results;
                        }
                    }
                }
            }

            int countResult = 0;

            while (rset.next() && (countResult++ < getMaxResult())) {
                results.add(buildResultObject(rset));
            }
            setResult(results); // cache result list
            em.afterExecuteQuery(this,true);
            return results;
        }
        catch (SQLException e) {
            em.afterExecuteQuery(this,false);
            throw new PersistenceException("SQLQuery.getResultList exception.", e);
        }
        finally {
            //close PreparedStatement
            try {
                if (rset != null) {
                    rset.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (pst != null) {
                    pst.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Object getSingleResult() {
        em.beforeExecuteQuery(this); // will call query.setResult() by CacheQuerySynchronization if result was cached
        if (getResult() != null) {
            return getResult();
        }

        PreparedStatement pst = null;
        ResultSet rset = null;
        try {
            pst = buildPreparedStatement();
            rset = pst.executeQuery();

            if (rset.next()) {
                Object result = buildResultObject(rset);
                setResult(result);
                em.afterExecuteQuery(this,true);
                return result;
            }
            else {
                return null;
            }
        }
        catch (SQLException e) {
            em.afterExecuteQuery(this,false);
            throw new PersistenceException("SQLQuery.getSingleResult exception.", e);
        }
        finally {
            //close PreparedStatement
            try {
                if (rset != null) {
                    rset.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (pst != null) {
                    pst.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 使用 velocity 构造 PreparedStatement，将 args 设置到 PreparedStatement 的 Parameter 中
     * Parameter 使用 p1 p2 p3 编号
     *
     * @throws SQLException sql exception
     */
    protected PreparedStatement buildPreparedStatement() throws SQLException {
        // velocity expressions
        final List<String> expressions = new ArrayList<String>();
        // expression's result
        final List<Object> expressionResults = new ArrayList<Object>();

        // nativeSQL 不一定是固定的，比如有 if 判断的时候，所以每次都要生成
        nativeSQL = VelocityUtils.evaluate(sqlTemplate.getTemplateSQL(em.getEntityManagerFactory().getDatabaseProductName()), parameterMap, new ReferenceInsertionEventHandler() {
            public Object referenceInsert(String reference, Object value) {
                if(sqlTemplate instanceof GeneratedNamedSQLTemplate) {
                    // if is GeneratedNamedSQLTemplate，从 columnMetaData取值
                    String columnName;
                    if(reference.indexOf(".") > 0) {
                        // select * from TABLE where acolumn = $TABLE.acolumn
                        columnName = reference.substring(reference.indexOf(".") + 1);
                    }
                    else {
                        // select * from TABLE where acolumn = $acolumn
                        columnName = reference.substring(1);
                    }
                    ColumnMetadata columnMetadata = EntityRepository.getInstance().getEntityMetadata(((NamedSQLTemplate)sqlTemplate).getDefinedClass()).getColumnMetadata(columnName);
                    if(columnMetadata == null) {
                        throw new PersistenceException("No metadata corresponds with column: " + ((NamedSQLTemplate)sqlTemplate).getDefinedClass().getSimpleName() + "." + columnName);
                    }
                    if(columnMetadata instanceof JoinColumnMetadata) {
                        value = ((JoinColumnMetadata)columnMetadata).getReferenceValue(value);
                    }
                }
                expressions.add(reference);
                expressionResults.add(value);
                return "?"; 
            }
        });

        logger.debug("Building PreparedStatement by SQL: " + nativeSQL);

        Connection connection = getConnection();
        PreparedStatement pst = connection.prepareStatement(nativeSQL, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        for (int i = 0; i < expressions.size(); i++) {

            // 不能都是用 setString/setobject, 需要使用正确类型,但是该类型无法判断
            Object parameterResult = expressionResults.get(i);
            if (parameterResult == null) {
                String msg = "Get null while evalute " + expressions.get(i);
                if(isNamedQuery()) {
                    msg += " in named qeury: " + ((NamedSQLTemplate)sqlTemplate).getName();
                }
                logger.warn(msg);
            }
            jdbcMapper.setPreparedStatementObject(pst, i + 1, expressionResults.get(i));
        }

        return pst;

    }

    protected Object buildResultObject(ResultSet rset) throws SQLException {
        //需要判断 ResultClass 类型
        String resultsetMapping = sqlTemplate.getResultsetMapping();
        Class<?> resultClass = sqlTemplate.getResultClass();

        if(!resultsetMapping.equals("")){ //sqlResultsetMapping is set
            return buildMappingResultsetObject(rset, resultsetMapping);
        }
        else if (void.class.equals(resultClass) || Map.class.equals(resultClass) || HashMap.class.equals(resultClass)){ // default void resultClass, return Map
            return buildMapObject(rset);
        }
        else if(MappingEntity.class.isAssignableFrom(resultClass)){ // MappingEntity
            return EntityFactory.newMappingEntity(buildMapObject(rset));
        }
        else  if (!resultClass.isInterface() && resultClass.isAnnotationPresent(Entity.class)) { // common Entity
            return buildEntityObject(rset);
        }
        else if (resultClass.equals(String.class) || ClassUtils.isPrimitiveClass(resultClass) || ClassUtils.isPrimitiveWrapperClass(resultClass)) { // primitive Class
            return jdbcMapper.getResultSetObject(rset, resultClass, 1);
        }
        else {
            throw new PersistenceException("Not supported result class: " + resultClass);
        }
    }

    // build object by ResultsetMaping
    protected Object buildMappingResultsetObject(ResultSet rset, String sqlResultsetMapping) throws SQLException {
        if(!QueryRepository.getInstance().containsResultsetMapping(sqlResultsetMapping)) {
            throw new ResultsetMappingNotFoundException(sqlResultsetMapping);
        }
        ResultsetMapping resultsetMapping = QueryRepository.getInstance().getResultsetMapping(sqlResultsetMapping);
        List<EntityResultMapping> entityResultMappings = resultsetMapping.getEntityResults();
        List<String> columnResultMappings = resultsetMapping.getColumnResults();

        List<Object> resultList = new ArrayList<Object>(entityResultMappings.size() + columnResultMappings.size());

        final Map<String, Object> resultMap = new HashMap<String, Object>();
        ResultSetMetaData rsetMeta = rset.getMetaData();

        //build columns
        for (int i = 1; i <= rsetMeta.getColumnCount(); i++) {
//            String columnName = rsetMeta.getColumnName(i); // Name 取的原始名
            String columnName = rsetMeta.getColumnLabel(i); // Label 取别名
            int index = columnResultMappings.indexOf(columnName);
            String className = rsetMeta.getColumnClassName(i);
            if(columnResultMappings.contains(columnName.toUpperCase())){
                try {
                    Class clazz = ClassUtils.getClass(className);
                    resultList.add(entityResultMappings.size() + index, jdbcMapper.getResultSetObject(rset, clazz, i));
                }
                catch (ClassNotFoundException e) {
                    throw new SQLException("Failed to build returned Map Object for " + toString() + ", columnName is " + columnName + ", column class name is " + className, e.getMessage());
                }
            }
            else { // put into resultMap for constructing Entity
                Class columnClass = sqlTemplate.getColumnClass(columnName);
                if(columnClass == null) {
                    logger.warn("No entity contains column \"" + columnName + "\", ignore this column.");
                }
                else {
                    resultMap.put(columnName, jdbcMapper.getResultSetObject(rset, columnClass, i));
                }
            }
        }
        //build entities by resultMap
        for(int i=0; i<entityResultMappings.size(); i++){
            Map<String, Object> localResultMap = new HashMap<String, Object>(resultMap);
            EntityResultMapping entityResultMapping = entityResultMappings.get(i);
            for(FieldResultMapping fieldResult : entityResultMapping.getFieldResultMappings()){
                //parse @FieldResult, set alias
                localResultMap.put(fieldResult.getName(), localResultMap.remove(fieldResult.getColumn().toUpperCase()));
            }
            Class entityClass = entityResultMapping.getEntityClass();
            Object entityObject = EntityFactory.newEntityObject(entityClass, localResultMap);
            
            //TODO: fetch @JoinColumn in EntityClass ???
            resultList.add(i, entityObject);
        }
        return resultList;
    }

    // return Map
    protected Map<String,Object> buildMapObject(ResultSet rset) throws SQLException {
        final Map<String, Object> resultMap = new HashMap<String, Object>();
        ResultSetMetaData rsetMeta = rset.getMetaData();

        //注意： 有可能只查部分字段，不能使用 sqlTemplate.getColumnsByResultClass()
        for (int i = 1; i <= rsetMeta.getColumnCount(); i++) {
            String columnName = rsetMeta.getColumnLabel(i); // Label 取别名
            String className = rsetMeta.getColumnClassName(i);
            try {
//            String columnName = rsetMeta.getColumnName(i); // Name 取的原始名
                Class clazz = ClassUtils.getClass(className);
                resultMap.put(columnName, jdbcMapper.getResultSetObject(rset, clazz, i));
            }
            catch (ClassNotFoundException e) {
                throw new SQLException("Failed to build returned Map Object for " + toString() + ", columnName is " + columnName + ", column class name is " + className, e.getMessage());
            }
        }
        return resultMap;
    }

    /**
     * build EntityObject according to resultClass
     *
     * @param rset result set
     * @throws SQLException if failed
     */
    protected Object buildEntityObject(ResultSet rset) throws SQLException {
        // get ResultMeta first then transfer to EntityFactory to build
        final Map<String, Object> resultMap = new HashMap<String, Object>();
        Class<?> resultClass = sqlTemplate.getResultClass();
        ResultSetMetaData rsetMeta = rset.getMetaData();

        //注意： 有可能只查部分字段，不能使用 sqlTemplate.getColumnsByResultClass()
        for (int i = 1; i <= rsetMeta.getColumnCount(); i++) {
//            String columnName = rsetMeta.getColumnName(i); // Name 取的原始名
            String columnName = rsetMeta.getColumnLabel(i); // Label 取别名
            Class columnClass = sqlTemplate.getColumnClass(columnName);
            ColumnMetadata columnMetadata = EntityRepository.getInstance().getColumnMetadata(resultClass, columnName);
            if(columnMetadata.isJoinColumn()) {
                //use referenced column class                
                Class<?> targetColumnClass = columnMetadata.getFieldType();
                ColumnMetadata joinColumnMetadata = EntityRepository.getInstance().getColumnMetadata(targetColumnClass, ((JoinColumnMetadata)columnMetadata).getReferencedColumnName());
                columnClass = joinColumnMetadata.getFieldType();
            }
            resultMap.put(columnName, jdbcMapper.getResultSetObject(rset, columnClass, i));
        }

        Object entityObject;
        entityObject = EntityFactory.newEntityObject(resultClass, resultMap);
        // deal with MappedColumn
        final Map<String, Object> mappedColumnResultMap = new HashMap<String, Object>();
        boolean hasJoinColumn = false; // should no joinColumn for void.class and MappingEntity.class
        for (JoinColumnMetadata joinColumnMetadata : sqlTemplate.getJoinColumnMetadatas()) {
            hasJoinColumn = true;

            // child Query share connection with parent query
            QueryExt mappedColumnQuery = new SQLQuery(getEntityManager(), sqlTemplate.createJoinColumnQueryNamedSQLTemplate(joinColumnMetadata), getConnection());

            for(Map.Entry<String, Object> entry : resultMap.entrySet()){
                // 将 resultMap 作为 Parameter
                mappedColumnQuery.setParameter(entry.getKey(), entry.getValue());
            }

            if (joinColumnMetadata.isToMany()) { // Collection or array
                mappedColumnResultMap.put(joinColumnMetadata.getName(), mappedColumnQuery.getResultList());
            }
            else { // single
                mappedColumnResultMap.put(joinColumnMetadata.getName(), mappedColumnQuery.getSingleResult());
            }
        }
        if (hasJoinColumn) {
            EntityFactory.appendJoinColumnValue(entityObject, mappedColumnResultMap);
        }
        return entityObject;
    }

    /**
     * create connection， reuse Connection in or one query one transaction
     */
    protected synchronized Connection getConnection() throws SQLException{
        if(connection != null) {
            if(connection.isClosed()) {
                connection = null;
            }
        }
        if(connection == null) {
            Connection conn = null;
            // try reuse connection from TxConnectionManager first
            if (em.getTransaction().isActive()) {
                conn = TransactionConnectionManager.reuseTransactionConnection(em.getUnitName(), ((EntityTransactionImpl)em.getTransaction()).getInternalTransaction());
            }
            if(conn != null && !conn.isClosed()) {
                this.connection = conn;
            }
            else { // create a new connection from datasource
                conn = em.getDataSource().getConnection();
                if (em.getTransaction().isActive()) {
                    // add to TransactionConnectionManager, transaction Synchronization will release all connection afeter transaction compolete
                    TransactionConnectionManager.addConnection2Tx(em.getUnitName(), conn, ((EntityTransactionImpl)em.getTransaction()).getInternalTransaction());
                }
                this.connection = conn;
            }
        }
//        return em.getDataSource().getConnection();
        return connection;
    }

    /**
     * close connection after query executed if no transaction. if has transaction, connection close automatically by Transaction
     * usually, not close connection in fact, just return to connection pool
     */
    protected synchronized void closeConnection() throws SQLException{
        if(connection != null) {
            try {
                connection.close();
            }
            finally {
                connection = null;
            }
        }
    }

    public CacheFactory getCacheFactory() {
        return em.getEntityManagerFactory().getCacheFactory();
    }

    public String getUnitName(){
            return em.getUnitName();
    }

    EntityManagerExt getEntityManager(){
        return em;
    }

    @Override
    public String toString() {
        return "Query{name=" + getName() + ", sql=" + sqlTemplate.getTemplateSQL();
    }
}
